//
// Copyright (c) 2010
// by Chris Paterra
//
// Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated 
// documentation files (the "Software"), to deal in the Software without restriction, including without limitation 
// the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and 
// to permit persons to whom the Software is furnished to do so, subject to the following conditions:
//
// The above copyright notice and this permission notice shall be included in all copies or substantial portions 
// of the Software.
//
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED 
// TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL 
// THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF 
// CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER 
// DEALINGS IN THE SOFTWARE.
//

using System;
using System.Data;
using Microsoft.ApplicationBlocks.Data;

namespace DotNetNuke.ContentLibrary.Components.Data
{

	/// <summary>
	/// SQL Server implementation (ie. concrete provider) of the DataProvider interface.
	/// </summary>
	public class SqlDataProvider : IDataProvider
	{

		#region Private Members

		private const string ProviderType = "data";
		private const string ModuleQualifier = "DCL_";
		private string _connectionString = String.Empty;
		private string _databaseOwner = String.Empty;
		private string _objectQualifier = String.Empty;

		#endregion

		#region Properties

		public string ConnectionString
		{
			get {
				return string.IsNullOrEmpty(_connectionString) ? DotNetNuke.Data.DataProvider.Instance().ConnectionString : _connectionString;
			}
			set { _connectionString = value; }
		}

		public string DatabaseOwner
		{
			get {
				return string.IsNullOrEmpty(_databaseOwner) ? DotNetNuke.Data.DataProvider.Instance().DatabaseOwner : _databaseOwner;
			}
			set { _databaseOwner = value; }
		}

		public string ObjectQualifier
		{
			get {
				return string.IsNullOrEmpty(_objectQualifier) ? DotNetNuke.Data.DataProvider.Instance().ObjectQualifier : _objectQualifier;
			}
			set { _objectQualifier = value; }
		}

		#endregion

		#region Private Methods

		private static object GetNull(object field)
		{
			return DotNetNuke.Common.Utilities.Null.GetNull(field, DBNull.Value);
		}

		private string GetFullyQualifiedName(string name)
		{
			return DatabaseOwner + ObjectQualifier + ModuleQualifier + name;
		}

		#endregion

		#region Public Methods

		#region Child Type

		public IDataReader GetChildTypes() {
			return SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("ChildType_GetAll"));
		}

		public int AddChildType(string childType, DateTime createdOnDate) {
			return Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, GetFullyQualifiedName("ChildType_Add"), childType, createdOnDate));
		}

		#endregion

		#region Comment

		public IDataReader GetAllComments(int contentItemID) {
			return SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("Comment_GetAll"), contentItemID);
		}

		public IDataReader GetApprovedComments(int contentItemID, int childKeyID, int childTypeID, int pageSize, int pageIndex) {
			return SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("Comment_GetApproved"), contentItemID, childKeyID, childTypeID, pageSize, pageIndex);
		}

		public IDataReader GetComment(int commentID)
		{
			return SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("Comment_Get"), commentID);
		}

		public int AddComment(string title, string comment, string author, string email, string website, bool approved, bool indexed, int parentID, int contentItemID, int childKeyID, int childTypeID, int createdByUserID, DateTime createdOnDate, int approvedByUserID, DateTime approvedOnDate)
		{
			return Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, GetFullyQualifiedName("Comment_Add"), GetNull(title), comment, GetNull(author), GetNull(email), GetNull(website), approved, indexed, GetNull(parentID), contentItemID, childKeyID, childTypeID, createdByUserID, createdOnDate, GetNull(approvedByUserID), GetNull(approvedOnDate)));
		}

		public void UpdateComment(int commentID, string title, string comment, string author, string email, string website, bool approved, bool indexed, int parentID, int contentItemID, int approvedByUserID, DateTime approvedOnDate, int lastModifiedByUserID, DateTime lastModifiedOnDate)
		{
			SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("Comment_Update"), commentID, GetNull(title), comment, GetNull(author), GetNull(email), GetNull(website), approved, indexed, GetNull(parentID), contentItemID, GetNull(approvedByUserID), GetNull(approvedOnDate), lastModifiedByUserID, lastModifiedOnDate);
		}

		public void DeleteComment(int commentID, int contentItemID)
		{
			SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("Comment_Delete"), commentID, contentItemID);
		}

		#endregion

		#region Favorite

		public IDataReader GetUserFavorites(int userID, int portalID) {
			return SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("Favorite_GetUserByPortal"), userID, portalID);
		}

		public IDataReader GetUserFavoritesByContentType(int userID, int portalID, int contentTypeID, int pageSize, int pageIndex) {
			return SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("Favorite_GetUserByType"), userID, portalID, contentTypeID, pageSize, pageIndex);
		}

		public void UpdateUserFavorite(string title, int contentItemID, int createdByUserID, DateTime createdOnDate) {
			SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("Favorite_Update"), title, contentItemID, createdByUserID, createdOnDate);
		}

		#endregion

		#region Rating

		public IDataReader GetUserRatings(int userID, int portalID) {
			return SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("Rating_GetUserByPortal"), userID, portalID);
		}

		public void UpdateUserRating(decimal ratingValue, int contentItemID, int childKeyID, int childTypeID, int createdByUserID, DateTime createdOnDate) {
			SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("Rating_Update"), ratingValue, contentItemID, childKeyID, childTypeID, createdByUserID, createdOnDate);
		}

		#endregion

		#region Related Item



		#endregion

		#region Review

		public IDataReader GetApprovedReviews(int contentItemID, int childKeyID, int childTypeID) {
			return SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("Review_GetApproved"), contentItemID, childKeyID, childTypeID);
		}

		public IDataReader GetUserReviews(int userID, int portalID) {
			return SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("Review_GetUserByPortal"), userID, portalID);
		}

		public IDataReader GetReview(int reviewID) {
			return SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("Review_Get"), reviewID);
		}

		public int AddReview(string title, string proComment, string conComment, string comment, bool approved, bool indexed, int contentItemID, int childKeyID, int childTypeID, int reviewMode, int createdByUserID, DateTime createdOnDate, int approvedByUserID, DateTime approvedOnDate) {
			return Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, GetFullyQualifiedName("Review_Add"), GetNull(title), GetNull(proComment), GetNull(conComment), GetNull(comment), approved, indexed, contentItemID, childKeyID, childTypeID, reviewMode, createdByUserID, createdOnDate, GetNull(approvedByUserID), GetNull(approvedOnDate)));
		}

		public void UpdateReview(int reviewID, string title, string proComment, string conComment, string comment, bool approved, bool indexed, int contentItemID, int approvedByUserID, DateTime approvedOnDate, int lastModifiedByUserID, DateTime lastModifiedOnDate) {
			SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("Review_Update"), reviewID, GetNull(title), GetNull(proComment), GetNull(conComment), GetNull(comment), approved, indexed, contentItemID, GetNull(approvedByUserID), GetNull(approvedOnDate), lastModifiedByUserID, lastModifiedOnDate);
		}

		public void DeleteReview(int reviewID, int contentItemID) {
			SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("Review_Delete"), reviewID, contentItemID);
		}

		// review rating
		public IDataReader GetReviewRating(int reviewID) {
			return SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("Review_Rating_Get"), reviewID);
		}

		public int AddReviewRating(int reviewID, decimal value, DateTime createdOnDate) {
			return Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, GetFullyQualifiedName("Review_Rating_Add"), reviewID, value, createdOnDate));
		}

		public void UpdateReviewRating(int reviewRatingID, int reviewID, decimal value, DateTime lastModifiedOnDate) {
			SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("Review_Rating_Update"), reviewID, value, lastModifiedOnDate);
		}

		#endregion

		#endregion

	}
}